package com.symc.sims0213xfw.dao.impl;

import com.symc.sims0213xfw.bean.Classes;
import com.symc.sims0213xfw.dao.ClassesDao;
import com.symc.sims0213xfw.utils.JDBCUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;
import java.util.List;

/**
 * @Author: 凤文  沈阳医学院2019级医学信息工程 0213
 * @CreateTime: 2022/01/25 12:52
 * @Description: 班级表查询
 */
public class ClassesDaoImpl implements ClassesDao {
    private static QueryRunner qr = new QueryRunner(JDBCUtils.getDataSource());

    @Override
    public List<Classes> queryListVisibleClasses(Integer grade) {
        List<Classes> classes = null;
        String sql = "select id,name,did from classes where isVisible=1 and " +
                "grade=?";
        try {
            classes = qr.query(sql,
                    new BeanListHandler<Classes>(Classes.class),grade);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return classes;
    }

    @Override
    public List<Classes> queryListClassesGradeByDepartmentName(String departmentName) {
        String sql = "SELECT DISTINCT c.grade FROM classes c,department d " +
                "WHERE d.name=? AND c.did=d.id";
        List<Classes> classes = null;
        try {
            classes = qr.query(sql,new BeanListHandler<Classes>(Classes.class),
                    departmentName);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return classes;
    }

    @Override
    public List<Classes> queryListClassesNameByGrade(Integer grade) {
        String sql = "SELECT NAME FROM classes WHERE grade=?";
        List<Classes> classes = null;
        try {
            classes = qr.query(sql,new BeanListHandler<>(Classes.class),grade);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return classes;
    }

    @Override
    public Classes queryClassesId(String departmentName, Integer grade, String className) {
        String sql = "SELECT c.id FROM classes c, department d WHERE d" +
                ".name=? AND c.grade=? AND c.name=?";
        Classes classes = null;
        try {
            classes= qr.query(sql,new BeanHandler<>(Classes.class),
                    new Object[]{departmentName,grade,className});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return classes;
    }

    @Override
    public Classes queryVisibleClasses(String id) {
        String sql = "select * from classes where id=? and isVisible=1";
        Classes classes = null;
        try {
            classes = qr.query(sql, new BeanHandler<>(Classes.class),id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return classes;
    }

    @Override
    public int addClasses(String id, String name, Integer did, Integer grade) {
        String sql = "INSERT INTO classes VALUES(?,?,?,?,1);";
        int i = 0;
        try {
            i = qr.update(sql, new Object[]{id, name, did, grade});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int updateClasses(String id, String name, Integer did,
                             Integer grade) {
        String sql = "UPDATE classes SET NAME=?,did=?,grade=?,isVisible=1 " +
                "WHERE id=?";
        int i = 0;
        try {
            i = qr.update(sql, new Object[]{name, did, grade, id});
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int deleteClasses(String id) {
        String sql = "UPDATE classes SET isVisible=0 WHERE id=?";
        int i = 0;
        try {
            i = qr.update(sql, id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }
}
